﻿CREATE  TRIGGER [dbo].[trgU_dtCity_calchrccrt] ON [dbo].[dtCity] FOR UPDATE
AS
IF UPDATE(CodeID)
BEGIN
  RAISERROR('Updates to Dep_id not allowed!', 16, 1)
  ROLLBACK TRAN
END
ELSE
IF UPDATE(mgrid)
BEGIN
  UPDATE E
  SET hrclvl = E.hrclvl - I.hrclvl + CASE
                              WHEN I.mgrid IS NULL THEN 0
                              ELSE M.hrclvl + 1
                            END,
      hrccrt = ISNULL(M.hrccrt, '.') +
                  CAST(I.CodeID AS varchar(10)) + '.' +
                  right(E.hrccrt, len(E.hrccrt) - len(I.hrccrt))
  FROM dtCity AS E JOIN INSERTED AS I
    ON E.hrccrt LIKE I.hrccrt + '%'
    LEFT OUTER JOIN dtCity AS M
    ON I.mgrid = M.CodeID
END


